-- =============================================
-- This procedure deletes specified content record from Content management system.
--
-- Modifications
-- 07/11/2003    E.Tatsui
-- 03/23/2004    E.Tatsui   Moved update statement to reset Default Content ID for a nav item
--                          to the end, because it shouldn't be run for "Revert" action.
-- =============================================

CREATE                                   PROCEDURE [dbo].[amsp_CMDeleteContent]
  @InContentID numeric,
  @InContactID numeric,
  @InRevertToPublishVerFlag char(1) = 'N',
  @OutPreviousContentID numeric OUTPUT
AS

BEGIN

  DECLARE
    @WorkflowStatusCode char(1),
    @PreviousContentID numeric,
    @DeletedFlag char(1),
    @NewDefaultContentID numeric,
    @NavMenuID numeric,
    @SortOrder numeric,
    @PreviousSortOrder numeric,
    @PreviousNavMenuID numeric,
    @NavContentGroupInd char(1),
    @DefaultContentID numeric,
    @RepublishNavItem bit

  SET @DeletedFlag = 'N'
  SET @RepublishNavItem = 0

  SELECT @WorkflowStatusCode = a.WorkflowStatusCode,
         @PreviousContentID = a.PreviousContentID,
         @NavMenuID = a.NavMenuID,
         @SortOrder = a.SortOrder,
         @NavContentGroupInd = b.NavContentGroupInd,
         @DefaultContentID = b.ContentID
    FROM Content a WITH (NOLOCK), Nav_Menu b WITH (NOLOCK)
   WHERE a.ContentID = @InContentID
     AND a.NavMenuID = b.NavMenuID

  -- If this is the default content record for any nav item, designate another content record (if any)
  -- as the default content.
  IF @NavContentGroupInd = 'N' AND @InRevertToPublishVerFlag = 'N'
     AND @DefaultContentID = @InContentID BEGIN
    SELECT TOP 1 @NewDefaultContentID = ContentID
      FROM vCurrent_Content WITH (NOLOCK)
     WHERE NavMenuID = @NavMenuID
       AND ContentID <> @InContentID
     ORDER BY SortOrder
  
    UPDATE Nav_Menu
       SET ContentID = @NewDefaultContentID
     WHERE NavMenuID = @NavMenuID

    SET @RepublishNavItem = 1
  END
  ELSE IF @NavContentGroupInd = 'C' AND @DefaultContentID = @InContentID
     UPDATE Nav_Menu
        SET ContentID = NULL
      WHERE NavMenuID = @NavMenuID


  -- WorkflowStatus is Published
  IF @WorkflowStatusCode = 'P' BEGIN
    -- Delete prevoius publishing requests for this content.
    DELETE FROM Publish_Request_Detail WHERE ContentID = @InContentID

    -- Put the content in recycled status.
    UPDATE Content
       SET WorkflowStatusCode = 'D',
           ContactID = @InContactID
     WHERE ContentID = @InContentID

    INSERT
      INTO Content_Workflow_Log (
           ContentID,
           WorkflowStatusCode,
           ContactID,
           ChangeDateTime)
    VALUES (@InContentID,
           'D',
            @InContactID,
            CURRENT_TIMESTAMP)

    -- We need to remove this from file system. Request to publishing server.
    EXEC amsp_CMRequestPublishContent @InContentID, @InContactID, NULL

    SET @DeletedFlag = 'Y'
  END
  -- Working/approved/pending approval content
  ELSE IF @WorkflowStatusCode IN ('W','A','E','Q') BEGIN
    
    BEGIN TRAN
    -- Delete the content. We don't need it any more.
    DELETE FROM Publish_Request_Detail WHERE ContentID = @InContentID
    DELETE FROM Content_HTML WHERE ContentID = @InContentID
    DELETE FROM Content_File WHERE ContentID = @InContentID
    DELETE FROM Content_Link WHERE ContentID = @InContentID
    DELETE FROM Content_Workflow_Log WHERE ContentID = @InContentID
    DELETE FROM Content_Security_Group WHERE ContentID = @InContentID
    DELETE FROM Component_Interest_Category WHERE ComponentID = @InContentID
    DELETE FROM Tagged_Page_Interest_Category WHERE ContentID = @InContentID
    DELETE FROM Content_Change_Request WHERE ContentID = @InContentID
    DELETE FROM Content WHERE ContentID = @InContentID
    COMMIT TRAN
  
    -- When we're simply deleting this, we want to make sure that the previous version
    -- is also recycled. Otherwise, previous version that is published is going to
    -- show up.
    IF @InRevertToPublishVerFlag = 'N' AND @PreviousContentID IS NOT NULL BEGIN

      UPDATE Content
         SET WorkflowStatusCode = 'D',
             ContactID = @InContactID
       WHERE ContentID = @PreviousContentID
  
      INSERT
        INTO Content_Workflow_Log (
             ContentID,
             WorkflowStatusCode,
             ContactID,
             ChangeDateTime)
      VALUES (@PreviousContentID,
             'D',
              @InContactID,
              CURRENT_TIMESTAMP)

      -- We need to remove this from file system. Request to publishing server.
      EXEC amsp_CMRequestPublishContent @PreviousContentID, @InContactID, NULL

     SET @DeletedFlag = 'Y'
    END  -- Deleting.
    -- When we are reverting to published version and this is nav menu.
    ELSE IF @NavContentGroupInd = 'N' BEGIN
      -- See what has been changed.
      SELECT @PreviousNavMenuID = NavMenuID,
             @PreviousSortOrder = SortOrder
        FROM Content WITH (NOLOCK)
       WHERE ContentID = @PreviousContentID
      
      -- If this contet has been moved within the same folder/menu,
      -- update previous version's sort order.
      IF @PreviousNavMenuID = @NavMenuID AND @PreviousSortOrder != @SortOrder
        UPDATE Content
           SET SortOrder = @SortOrder
         WHERE ContentID = @PreviousContentID
      -- If it has been moved, re-sort contents in the previous menu/folder.
      ELSE IF @PreviousNavMenuID != @NavMenuID
        EXEC amsp_CMRenumCurrentContent @PreviousNavMenuID
    END
  END -- Working/approved contents.

  IF @DeletedFlag = 'Y' AND @NavContentGroupInd = 'N' BEGIN
    -- If deleting this content record reduces the number of content records for the nav menu item to
    -- one, we must reset the DirectListComboInd to "D". This query will take care of every nav menu item.
    UPDATE Nav_Menu
       SET DirectListComboInd = 'D'
     WHERE NavMenuID IN (SELECT b.NavMenuID
                           FROM Nav_Menu b LEFT OUTER JOIN Content a
                             ON a.NavMenuID = b.NavMenuID
                          WHERE a.WorkflowStatusCode IN ('A','P')
                          GROUP BY b.NavMenuID
                         HAVING COUNT(a.NavMenuID) = 1)

    IF @RepublishNavItem = 1 BEGIN
      -- Re-publish the nav item.
      DECLARE @OutPublishRequestID numeric
      EXEC amsp_CMRequestPublish @InNavMenuID = @NavMenuID,
                                 @InContactID = @InContactID,
                                 @InPublishRegenerateInd = 'P',
                                 @OutPublishRequestID  = @OutPublishRequestID OUTPUT
    END
  END
  EXEC amsp_CMRenumCurrentContent @NavMenuID
  SET @OutPreviousContentID = @PreviousContentID
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMDeleteContent] TO [IMIS]
GO